#!/bin/sh
cd `dirname $0`
source ./../config.sh
exec_dir college_practice_num_radio

HIVE_DB=assurance33
HIVE_TABLE=college_practice_num_radio
TARGET_TABLE=im_quality_data_info
DATA_NO=XY_DGSXBL

function create_table() {
    hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
    hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
    hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
                data_no String comment '数据项编号',
                data_name String comment '数据项名称',
                data_cycle String comment '数据统计周期  YEAR 年  MONTH 月  DAY 日  QUARTER 季度 OTHER 其他',
                data_type String comment '数据类型（NUMBER或者ENUM）',
                data_time String comment '数据日期  年YYYY  月YYYYmm 日YYYYMMDD  季度YYYY-1，yyyy-2,yyyy-3,yyyy-4   学期 yyyy-yyyy  学期 yyyy-yyyy-1,yyyy-yyyy-2',
                data_value String comment '数据项值（数字保存数字，如果是数据字典枚举保存key）',
                is_new String comment '是否最新 是YES 否NO',
                create_time String comment '创建时间'
    ) COMMENT '顶岗实习比例'
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"
}

function import_table() {
    hive -e "
         INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
            SELECT
              c.data_no as data_no,
              c.data_name as data_name,
              c.data_cycle as data_cycle,
              c.data_type as data_type,
              concat(b.semester_year,'-',b.semester) as data_time,
              cast(
                (a.num / b.num) as DECIMAL(6, 2)
              ) as data_value,
              'NO' as is_new,
              FROM_UNIXTIME(
                UNIX_TIMESTAMP()
              ) AS create_time
            FROM
              (
                SELECT
                  sum(a.student_count) as num,
                  a.semester_year
                FROM
                  app.major_plan_student a
                WHERE
                  a.type = '3'
                GROUP BY
                  a.semester_year
              ) a,
              (
                select
                  a.semester_year,
                  a.semester,
                  count(a.code) as num
                from
                  app.basic_semester_student_info a
                GROUP BY
                 a.semester_year,
                  a.semester
              ) b,
              assurance33.im_quality_data_base_info c
            where
              c.data_no = '${DATA_NO}'
              and a.semester_year= b.semester_year
         "
}

function import_table_new() {
    hive -e "
         INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
            SELECT
              c.data_no as data_no,
              c.data_name as data_name,
              c.data_cycle as data_cycle,
              c.data_type as data_type,
              concat(b.semester_year,'-',b.semester) as data_time,
              cast(
                (a.num / b.num) as DECIMAL(6, 2)
              ) as data_value,
              'NO' as is_new,
              FROM_UNIXTIME(
                UNIX_TIMESTAMP()
              ) AS create_time
            FROM
              (
                SELECT
                  sum(a.student_count) as num,
                  a.semester_year
                FROM
                  app.major_plan_student a
                WHERE
                  a.type = '3'
                GROUP BY
                  a.semester_year
              ) a,
              (
                select
                  a.semester_year,
                  a.semester,
                  count(a.code) as num
                from
                  app.basic_semester_student_info a
                GROUP BY
                 a.semester_year,
                  a.semester
              ) b,
              assurance33.im_quality_data_base_info c
            where
              c.data_no = '${DATA_NO}'
              and a.semester_year= b.semester_year
            order by data_time desc limit 1
         "
}

function export_table() {
    DATE_TIME=`hive -e "select max(data_time) from ${HIVE_DB}.${HIVE_TABLE}"`
    clear_mysql_data "
                delete from
                  im_quality_data_info
                where
                 data_no = '${DATA_NO}'
                  ;
                "

    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'data_no,data_name,data_cycle,data_type,data_time,data_value,is_new,create_time'

    clear_mysql_data "update im_quality_data_info set is_new = 'NO' where data_no = '${DATA_NO}';"
    clear_mysql_data "update im_quality_data_info set is_new = 'YES' where data_no = '${DATA_NO}' and data_time='${DATE_TIME}'"
}

function export_table_new(){
  DATE_TIME=`hive -e "select max(data_time) from ${HIVE_DB}.${HIVE_TABLE}"`
    clear_mysql_data "
                delete from
                  im_quality_data_info
                where
                  data_no = '${DATA_NO}'
                  and data_time = '${DATE_TIME}';
                "

    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'data_no,data_name,data_cycle,data_type,data_time,data_value,is_new,create_time'

    clear_mysql_data "update im_quality_data_info set is_new = 'NO' where data_no = '${DATA_NO}';"
    clear_mysql_data "update im_quality_data_info set is_new = 'YES' where data_no = '${DATA_NO}' and data_time='${DATE_TIME}'"
}

#验证脚本的时候脚本里面的方法都要进行执行一次
#各方法的意思
#create_table 创建表
#import_table  导入数据（含历史）
#export_table  导出数据 （含历史）
#import_table_new 导入数据（不含历史）
#export_table_new 导出数据 （不含历史）
#获取要处理的表
# sh -x 脚本名 1
if [ $# == 1 ]
    then
    create_table
    import_table
    export_table
    finish
else
  echo "shell脚本里面的方法:"
fi


